package com.qin.dao;

import java.sql.SQLException;
import java.util.List;

import com.qin.entity.Book;

import cn.hutool.db.Db;
import cn.hutool.db.Entity;
import cn.hutool.db.handler.BeanHandler;

/**
 * 完成对Book实体类的操作
 * 
 * @author BestRiven
 *
 */
public class BookDao {

	/**
	 * 插入图书
	 * @param book 要插入图书的信息
	 * @return 返回自增id
	 */
	public Long insertBook(Book book) {
		Entity record = Entity.parseWithUnderlineCase(book);
		Long insertForGeneratedKey = 0L;
		try {
			insertForGeneratedKey = Db.use().insertForGeneratedKey(record);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return insertForGeneratedKey;
	}
	
	/**
	 * 根据id逻辑删除对应的图书
	 * @param id 要删除图书的id
	 * @return 影响行数（0代表失败，1代表影响一行）
	 */
	public int deleteBook(Long id) {
		String sql = "UPDATE `book` SET `status` = 0 WHERE id = ?";
		int del = 0;
		try {
			del = Db.use().execute(sql, id);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return del;
	}
	
	/**
	 * 拿到要修改的图书的id，根据传过来的图书动态sql拼串，修改图书
	 * @param id
	 * @param book
	 * @return
	 */
	public int updateBook(Long id,Book book) {
		Entity record = Entity.parse(book, true, true);
		int update = 0;
		try {
			update = Db.use().update(record, Entity.create("book").set("id", id).set("status", 1));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return update;
	}
	
	/**
	 * 通过id查询图书
	 * @param id 要查询图书的id
	 * @return 查询得到的图书（未查到返回null）
	 */
	public Book querryBookById(Long id) {
		Entity where = Entity.create("book").set("id", id).set("status", 1);
		Book book = null;
		try {
			book = Db.use().find(where, new BeanHandler<Book>(Book.class));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return book;
	}
	
	/**
	 * 从折扣小于等于5的图书中，随机查出n个数据
	 * @param n 数据个数
	 * @return 所有图书
	 */
	public List<Book> getRecommendBooks(Integer n){
		List<Book> books = null;
		String sql = "SELECT * FROM `book` WHERE `discount` <= 5.0 AND `status` = 1 ORDER BY RAND() LIMIT ?";
		try {
			books = Db.use().query(sql, Book.class, n);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return books;
	}
	/**
	 * 按照id排序，查询前四条数据，默认四大名著
	 * @return 四大名著
	 */
	public List<Book> getMasterWork(){
		List<Book> books = null;
		String sql = "SELECT * FROM `book` WHERE `status` = 1 GROUP BY `id` LIMIT 4";
		try {
			books = Db.use().query(sql, Book.class);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return books;
	}
	
	/**
	 * 根据图书分类的id，查询图书
	 * @return
	 */
	public List<Book> getBooksByCategoryId(Long id){
		List<Book> books = null;
		try {
			books = Db.use().find(Entity.create("book").set("category_id", id).set("status", 1),Book.class);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return books;
	}
	
	/**
	 * 查询所有的图书
	 * @return 所有的图书，用List封装
	 */
	public List<Book> getAllBooks(){
		List<Book> books = null;
		try {
			books = Db.use().find(Entity.create("book").set("status", 1),Book.class);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return books;
	}
}
